1. Field of the Invention
This invention relates in general to computer-implemented database systems, and, in particular, to deferred referential integrity checking.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on direct access storage devices (DASD) such as magnetic or optical disk drives for semipermanent storage.
RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO). The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages, such as C and COBOL. SQL allows the user to manipulate the data.
The definitions for SQL provide that a RDBMS should respond to a particular query with a particular set of data given a specified database content, but the method that the RDBMS uses to actually find the required information in the tables on the disk drives is left up to the RDBMS. Typically, there will be more than one method that can be used by the RDBMS to access the required data. The RDBMS will optimize the method used to find the data requested in a query in order to minimize the computer time used and, therefore, the cost of doing the query.
A table can have a key, which uniquely identifies each row in the table. For example, for an Employee table that holds employee information, a social security number column could be the parent key. When the key is referenced in a referential integrity relationship, the key is said to be a "parent" key. The parent key could be made up of one or more columns and is defined by a developer who creates the table. Additionally, a key (a "foreign" or "dependent" key) in one table (a "dependent" or "child" table) can reference a key in other tables, called parent tables. The foreign key matches a parent key in the parent table. That is, relationships between tables are made through the values in the columns, and the columns that maintain this link are foreign keys. Referential integrity requires that every non-NULL foreign key value in a dependent table must have a matching parent key value in a parent table. For example, if a company has various offices, a Company table could have a Branch column as a parent key, and then an Employee table could have a foreign key that references the Branch column, indicating the branch at which a particular employee works. Referential integrity checking involves ensuring that foreign key values match parent key values. Therefore, when a parent key is updated or deleted, referential integrity checking ensures that corresponding foreign keys have been updated or deleted.
When a set of records are being deleted or updated, in tables involved in referential integrity relationships, the order in which the rows are updated or deleted may determine whether the delete or update will be successful. This order is determined based on the access path selected, which, in turn, depends on performance and other considerations. This is true if referential integrity checking is performed as each row is updated or deleted. When referential integrity checking is unsuccessful, the execution of the SQL statement is aborted and the table is returned to its original form. Some systems avoid this problem by restricting the execution of certain SQL statements, such as deleting from a self-referencing table and multiple-row updates on parent keys.
There is a need in the art for an improved method of referential integrity checking that would avoid unnecessary abortion of the execution of SQL statements.